require 'win32/clipboard'
include Win32

class PagingQueryGen
  def initialize(str)
    @str = str
  end
  def getFields
    splitstr = @str.scan(/select([\s\w\*\.\,\(\)]*)from/i).to_s.split(/\,/)
    formatting = splitstr.map {|x| x.strip}.map{|x| "\t" + x }
    formatting[0] = "\n"+ formatting[0]
    formatting.join(",\n")
  end
  def getTables
    @str.scan(/from([\s\w\=\.]+)where/i).to_s.split(' ').join(' ').gsub(/inner/, "\ninner")
  end
  def getGroupbyStmt
    @str.scan(/(group by[\s\.\w\,]*)order?/i).to_s.split(' ').join(' ').split(',').map{|x|x.strip}.join(', ')
  end
  def getWhereStmt
    @str.scan(/where([\.\s\=\w\'\%]+)group|order/i).to_s.strip
  end
  def getOrderbyStmt
    @str.scan(/order[\w\s\.]*/i)
  end
  def make(key)
"SELECT TOP 0 #{getFields}\nFROM #{getTables} 
WHERE #{key} NOT IN (
    SELECT TOP 10 #{key}
    FROM #{getTables}
    WHERE #{getWhereStmt}
    #{getGroupbyStmt}
    #{getOrderbyStmt}
)
AND #{getWhereStmt}
#{getGroupbyStmt}
#{getOrderbyStmt}"
  end
end

str = File.read('c:\\pagingtest.txt')
pg = PagingQueryGen.new(str)
Clipboard.set_data pg.make('E.EvalNo')

